from flask import Blueprint, request, jsonify
import logging
from mysql_tool.mysql_tool import MySQLTool
from datetime import datetime, timedelta

aoi_scan_bp = Blueprint('aoi_scan', __name__)

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

@aoi_scan_bp.route('/query_aoi_scan', methods=['POST'])
def query_aoi_scan():
    """
    AOI综合查询接口（按产线分开，1线/2线）
    主要功能和步骤如下：
    1. 查询指定产线（line_id）最近100条AOI记录，按创建时间倒序。
    2. 提取当前工单、机种（model_name）、订单数量（target_output）。
    3. 根据历史记录推算预计完成时间（如：用当前产出速率推算）。
    4. 统计当前工单已生产数量（实际产出）。
    5. 计算计划产出比（实际产出/计划产出）。
    6. 查询今日工作时间段（如8-10, 10-12, ..., 18-20），统计每个时间段的计划产出。
       - 若时间段未到，则实际产出为0或不统计。
    7. 计算每个时间段的完成率（实际产出/计划产出）。
    8. 计算当前工单AOI直通率（OK数/总数）与良品率（1 - NG/总数），并按每小时分组统计。
    9. 所有上述内容通过本接口一次性返回，1线和2线分开查询。
    """

    try:
        req_data = request.get_json()
        if not req_data:
            return jsonify({"success": False, "message": "请求数据不能为空"}), 400

        line_id = req_data.get('line_id')
        if not line_id:
            return jsonify({"success": False, "message": "line_id 不能为空"}), 400

        # 使用短连接模型：每次 execute_* 内部自动创建/关闭连接
        mysql_tool = MySQLTool()

        # 1. 最近记录
        sql_recent = """
            SELECT id, model_name, barcode, result, inspect_time, inspect_start_time, duration,
                   work_order, target_output, line_id, panel_count, capacity, created_at,
                   COALESCE(board_ok_count, 0) as board_ok_count,
                   COALESCE(board_ng_count, 0) as board_ng_count
            FROM smt_aoi_scan_record
            WHERE line_id = %s
            ORDER BY created_at DESC
            LIMIT 100
        """
        recent_results = mysql_tool.execute_query(sql_recent, (line_id,))

        records = [{
            "id": r[0], "model_name": r[1], "barcode": r[2], "result": r[3],
            "inspect_time": r[4], "inspect_start_time": r[5], "duration": r[6],
            "work_order": r[7], "target_output": r[8], "line_id": r[9],
            "panel_count": r[10], "capacity": r[11],
            "created_at": r[12].isoformat() if r[12] else None,
            "board_ok_count": float(r[13] or 0),
            "board_ng_count": float(r[14] or 0)
        } for r in recent_results]

        if not records:
            # 无数据时直接返回三层的占位结构
            return jsonify({
                "success": True,
                "data": {
                    "first_layer": {
                        "work_order": None,
                        "model_name": None,
                        "target_output": 0,
                        "produced_total": 0,
                        "produced_ok": 0,
                        "produced_ng": 0,
                        "remaining": 0,
                        "production_progress": 0,
                        "estimate_finish_time": None
                    },
                    "second_layer": {
                        "period_hours": int(req_data.get('period_hours', 1) or 1),
                        "periods": []
                    },
                    "third_layer": {
                        "overall_pass_rate": None,
                        "overall_yield_rate": None,
                        "hourly": []
                    }
                }
            })

        if records:
            current_work_order = records[0]["work_order"]
            current_model_name = records[0]["model_name"]
            current_target_output = records[0]["target_output"]
        else:
            current_work_order = None
            current_model_name = None
            current_target_output = 0

        # 指标计算分母口径（total 或 ok_ng）
        metric_denominator = (req_data.get('metric_denominator') or 'total').lower()
        if metric_denominator not in ('total', 'ok_ng'):
            metric_denominator = 'total'

        # 3A. 统计当日产出数量（按时间维度，不限制工单）
        sql_actual_output = """
            SELECT
                SUM(COALESCE(panel_count,0)) AS total_count,
                SUM(COALESCE(board_ok_count,0)) AS ok_count,
                SUM(COALESCE(board_ng_count,0)) AS ng_count
            FROM smt_aoi_scan_record
            WHERE line_id = %s AND DATE(created_at) = CURDATE()
        """
        actual_output = mysql_tool.execute_query(sql_actual_output, (line_id,))
        actual_output_count = float(actual_output[0][0] or 0) if actual_output else 0.0
        ok_output_count = float(actual_output[0][1] or 0) if actual_output else 0.0
        ng_output_count = float(actual_output[0][2] or 0) if actual_output else 0.0
        # 直通率严格按 Σboard_ok_count / Σpanel_count
        overall_denom = actual_output_count
        overall_pass_rate = (ok_output_count / overall_denom) if overall_denom and overall_denom > 0 else None
        # 良品率按 1 - (Σng / Σpanel)（若 NG 缺失，可能为 None 或 1.0）
        overall_yield_rate = (1 - (ng_output_count / overall_denom)) if overall_denom and overall_denom > 0 else None

        # 3B. 统计当前工单维度产出与进度（基础信息按工单统计）
        sql_actual_output_wo = """
            SELECT
                SUM(COALESCE(panel_count,0)) AS total_count,
                SUM(COALESCE(board_ok_count,0)) AS ok_count,
                SUM(COALESCE(board_ng_count,0)) AS ng_count
            FROM smt_aoi_scan_record
            WHERE line_id = %s AND work_order = %s
        """
        actual_output_wo = mysql_tool.execute_query(sql_actual_output_wo, (line_id, current_work_order))
        work_order_actual_output_count = float(actual_output_wo[0][0] or 0) if actual_output_wo else 0.0
        work_order_ok_output_count = float(actual_output_wo[0][1] or 0) if actual_output_wo else 0.0
        work_order_ng_output_count = float(actual_output_wo[0][2] or 0) if actual_output_wo else 0.0
        # 可选：工单整体直通率（目前未返回），若需可开启
        # work_order_overall_pass_rate = (
        #     work_order_ok_output_count / work_order_actual_output_count
        # ) if work_order_actual_output_count > 0 else None
        # work_order_overall_yield_rate = (
        #     1 - (work_order_ng_output_count / work_order_actual_output_count)
        # ) if work_order_actual_output_count > 0 else None

        # 4. 计划产出比（时间维度：当日）
        plan_output_ratio = (
            actual_output_count / current_target_output
            if current_target_output and current_target_output > 0 else 0
        )
        # 按工单的生产进度（工单维度）
        work_order_plan_output_ratio = (
            work_order_actual_output_count / current_target_output
            if current_target_output and current_target_output > 0 else 0
        )

        # 3C. 当前工单当日产出（用于计算当日速率）
        sql_actual_output_wo_today = """
            SELECT
                SUM(COALESCE(panel_count,0)) AS total_count,
                SUM(COALESCE(board_ok_count,0)) AS ok_count,
                SUM(COALESCE(board_ng_count,0)) AS ng_count
            FROM smt_aoi_scan_record
            WHERE line_id = %s AND work_order = %s AND DATE(created_at) = CURDATE()
        """
        actual_output_wo_today = mysql_tool.execute_query(sql_actual_output_wo_today, (line_id, current_work_order))
        work_order_actual_output_count_today = float(actual_output_wo_today[0][0] or 0) if actual_output_wo_today else 0.0

        # 5. 推算预计完成时间（按工单统计；速率=当日产出速率，剩余量=累计剩余）
        sql_time_range = """
            SELECT MIN(created_at), MAX(created_at)
            FROM smt_aoi_scan_record
            WHERE line_id = %s AND work_order = %s AND DATE(created_at) = CURDATE()
        """
        time_range = mysql_tool.execute_query(sql_time_range, (line_id, current_work_order))
        min_time, max_time = time_range[0] if time_range else (None, None)
        if min_time and max_time and work_order_actual_output_count_today > 0:
            time_delta = (max_time - min_time).total_seconds() / 3600
            rate = work_order_actual_output_count_today / time_delta if time_delta > 0 else 0
            remain = (current_target_output or 0) - work_order_actual_output_count
            est_finish = (max_time + timedelta(hours=remain / rate)) if rate > 0 else None
            est_finish_str = est_finish.strftime("%Y-%m-%d %H:%M:%S") if est_finish else None
        else:
            est_finish_str = None

        # 6. 工作时间段（08:00-20:00），period_hours 缺省1
        raw_period_hours = req_data.get('period_hours', 1)
        try:
            period_hours = int(raw_period_hours)
        except (TypeError, ValueError):
            period_hours = 1
        if period_hours < 1 or period_hours > 24:
            period_hours = 1

        work_start_hour = 8
        work_end_hour = 20
        work_periods = []
        h = work_start_hour
        while h < work_end_hour:
            end_h = min(h + period_hours, work_end_hour)
            work_periods.append((h, end_h))
            h = end_h

        today = datetime.now().date()

        # 6+. 按小时、按工单统计容量与产出（当天 08:00-20:00）
        sql_hourly_by_order = """
            SELECT
                HOUR(created_at) AS hour,
                work_order,
                MAX(model_name) AS model_name,
                COALESCE(ROUND(AVG(capacity),0),0) AS planned_capacity,
                SUM(COALESCE(board_ok_count,0)) AS ok_count,
                SUM(COALESCE(board_ng_count,0)) AS ng_count,
                SUM(COALESCE(panel_count,0)) AS total
            FROM smt_aoi_scan_record
            WHERE line_id = %s
                AND DATE(created_at) = CURDATE()
                AND TIME(created_at) >= '08:00:00'
                AND TIME(created_at) < '20:00:00'
            GROUP BY hour, work_order
            ORDER BY hour, work_order
        """
        hourly_by_order_rows = mysql_tool.execute_query(sql_hourly_by_order, (line_id,))

        # 组装每小时详细与汇总
        hourly_detail_map = {}
        hourly_pass_rate = []  # 汇总（保持兼容）
        for row in hourly_by_order_rows:
            hour, wo, model_name, planned_capacity, ok_c, ng_c, total = row
            hour = int(hour)
            planned_capacity = float(planned_capacity or 0)
            ok_c = float(ok_c or 0)
            ng_c = float(ng_c or 0)
            total = float(total or 0)
            # 单条（小时x工单）层面：仅保留原始 OK/NG/Total，直通率以 OK/Total 计算
            pass_rate = (ok_c / total) if total > 0 else None
            yield_rate = (1 - (ng_c / total)) if total > 0 else None
            item = {
                "work_order": wo,
                "model_name": model_name,
                "planned_capacity": planned_capacity,
                "ok_count": ok_c,
                "ng_count": ng_c,
                "total": total,
                "pass_rate": pass_rate,
                "yield_rate": yield_rate,
                # 计划产出（每小时）等于该小时计划节拍
                "plan_output": planned_capacity,
                "finish_rate": (total / planned_capacity) if planned_capacity > 0 else None
            }
            hourly_detail_map.setdefault(hour, []).append(item)

        # 生成每小时聚合（兼容旧字段）
        cumulative_total = 0.0
        cumulative_ok = 0.0
        cumulative_ng = 0.0
        for hour in sorted(hourly_detail_map.keys()):
            items = hourly_detail_map[hour]
            inst_total = sum(i["total"] for i in items)
            # 严格使用 Σboard_ok_count / Σpanel_count
            inst_ok = sum(i["ok_count"] for i in items)
            inst_ng = sum(i["ng_count"] for i in items)

            # 累计到当前小时
            cumulative_total += inst_total
            cumulative_ok += inst_ok
            cumulative_ng += inst_ng

            # 小时瞬时直通/良品
            instant_pass_rate = (inst_ok / inst_total) if inst_total > 0 else None
            instant_yield_rate = (1 - (inst_ng / inst_total)) if inst_total > 0 else None

            # 小时累计直通/良品（用户期望）
            cum_pass_rate = (cumulative_ok / cumulative_total) if cumulative_total > 0 else None
            cum_yield_rate = (1 - (cumulative_ng / cumulative_total)) if cumulative_total > 0 else None

            hourly_pass_rate.append({
                "hour": hour,
                # 当小时数据
                "total": inst_total,
                "ok_count": inst_ok,
                "ng_count": inst_ng,
                "instant_pass_rate": instant_pass_rate,
                "instant_yield_rate": instant_yield_rate,
                # 仅输出当小时指标；累计结果只在 overall_* 返回
            })

        # 期段统计：按 period_hours 聚合来自各小时的计划/实际（含多工单）
        period_stats = []
        period_stats_by_work_order = []
        for start_h, end_h in work_periods:
            # 聚合该期段的每小时数据
            hours_in_period = list(range(start_h, end_h))
            # 按工单聚合
            agg_by_wo = {}
            period_plan_total_effective = 0.0  # 每小时取该小时最大节拍，再跨小时相加
            period_actual_total = 0.0
            hour_plan_text_parts = []  # 展示用：同一小时用'/'拼接，多小时用' + '连接
            for h in hours_in_period:
                # 收集该小时的容量（节拍）列表
                hour_caps = []
                for item in hourly_detail_map.get(h, []):
                    wo = item["work_order"]
                    plan = float(item["plan_output"] or 0)
                    act = float(item["total"] or 0)
                    agg = agg_by_wo.setdefault(wo, {
                        "work_order": wo,
                        "model_name": item.get("model_name"),
                        "plan_output": 0.0,
                        "actual_output": 0.0
                    })
                    agg["plan_output"] += plan
                    agg["actual_output"] += act
                    period_actual_total += act
                    hour_caps.append(plan)

                # 该小时的有效计划 = 多工单时不相加，取最大节拍；展示文本为用'/'拼接
                if hour_caps:
                    hour_effective_plan = max(hour_caps)
                    period_plan_total_effective += hour_effective_plan
                    # 文本部分（整数显示更直观）
                    hour_caps_str = "/".join(str(int(c)) if float(c).is_integer() else str(c) for c in hour_caps)
                    hour_plan_text_parts.append(hour_caps_str)

            # 期段聚合（兼容旧字段）
            finish_rate = (period_actual_total / period_plan_total_effective) if period_plan_total_effective > 0 else 0
            period_stats.append({
                "period": f"{start_h:02d}:00-{end_h:02d}:00",
                # 数值：用于计算达成率的有效计划（每小时取最大节拍，再相加）
                "plan_output": period_plan_total_effective,
                # 文本：展示用，多工单同小时用'/'拼接，多小时用' + '连接
                "plan_output_text": " + ".join(hour_plan_text_parts) if hour_plan_text_parts else "0",
                "actual_output": period_actual_total,
                "finish_rate": finish_rate
            })

            # 期段工单明细
            breakdown = []
            for wo, v in agg_by_wo.items():
                plan = v["plan_output"]
                act = v["actual_output"]
                br_finish = (act / plan) if plan > 0 else None
                breakdown.append({
                    "work_order": wo,
                    "model_name": v.get("model_name"),
                    "plan_output": plan,
                    "actual_output": act,
                    "finish_rate": br_finish
                })
            period_stats_by_work_order.append({
                "period": f"{start_h:02d}:00-{end_h:02d}:00",
                "items": breakdown
            })

        return jsonify({
            "success": True,
            "data": {
                "first_layer": {
                    "work_order": current_work_order,
                    "model_name": current_model_name,
                    "target_output": current_target_output,
                    "produced_total": work_order_actual_output_count,
                    "produced_ok": work_order_ok_output_count,
                    "produced_ng": work_order_ng_output_count,
                    "remaining": max(0, (current_target_output or 0) - work_order_actual_output_count),
                    "production_progress": work_order_plan_output_ratio,
                    "estimate_finish_time": est_finish_str
                },
                "second_layer": {
                    "period_hours": period_hours,
                    "periods": period_stats
                },
                "third_layer": {
                    "overall_pass_rate": overall_pass_rate,
                    "overall_yield_rate": overall_yield_rate,
                    "hourly": hourly_pass_rate,
                    "metric_denominator": metric_denominator
                }
            }
        })

    except Exception as e:
        logger.exception("查询 AOI 扫描记录失败")
        return jsonify({
            "success": False,
            "message": f"查询失败: {str(e)}"
        }), 500
